using System;
using System.Data;
using System.Data.SqlClient; 
using System.Diagnostics;

namespace FilmekAdatbazis
{
	/// <summary>
	/// Summary description for DAL.
	/// </summary>
	public abstract class DAL
	{
		protected static bool kapcsolodva; 		
		protected static bool ConnectionCreated;
		protected static SqlConnection mySQLServerConn;
        protected string strSQLServConn = "Server=(local);Database=filmek;Trusted_Connection=True";
		protected System.Data.SqlClient.SqlDataReader myDataReader;
		protected System.Data.SqlClient.SqlCommand myComm;
        protected System.Data.DataSet myDataSet; 
        protected System.Data.SqlClient.SqlDataAdapter da;


		protected bool IsConnectCreated()
		{
			return ConnectionCreated;
		}
		protected bool IsConnected()
		{
			return kapcsolodva;
		}

		protected System.Data.SqlClient.SqlConnection theConnection()
		{ return mySQLServerConn;}
		protected bool MakeConnection()
		{
			// Create the Connection if is was not already created.
			if (ConnectionCreated != true)
			{
				try
				{
					mySQLServerConn = new SqlConnection(strSQLServConn);
					mySQLServerConn.Open();
					ConnectionCreated = true;
					mySQLServerConn.Close();
					kapcsolodva = false;
					return true;
				}
				catch (SqlException e)
				{
					String ErrMess = e.Message;
		
					return false;
				}
			}
			else {return true;}
		}

		protected SqlDataReader myExecuteReader(string sQuery, ref string ErrMess)
		{
			try 
			{
				this.OpenConnection();
				myComm = new SqlCommand(sQuery, mySQLServerConn); 
				myDataReader = myComm.ExecuteReader();	
				ErrMess = "OK";
				//			this.CloseConnection();
				return myDataReader;			

			}
			catch (SqlException e)
			{
				ErrMess = e.Message;
				this.CloseConnection();
				return null;
			}
		}
		/// <summary>
		/// Open the Connection when the state is not already open.
		/// </summary>
		protected bool OpenConnection()
		{
			// Open the Connection when the state is not already open.
			if (kapcsolodva != true)
			{
				try
				{
					mySQLServerConn.Open();
					kapcsolodva = true;		
					return true;
				}
				catch
				{
					return false;
				}
				
			}
			else {return true;}
		}
        protected DataSet FillDataSetFromSqlQuery(String sSQL, String sTableName, ref string Errm)
        {
            da = new System.Data.SqlClient.SqlDataAdapter();
            if (!IsConnected())
            {
                MakeConnection();
            }
            try
            {
                OpenConnection();
                da.TableMappings.Add("Table", sTableName);
                myComm = new SqlCommand(sSQL, theConnection());
                myComm.CommandType = CommandType.Text;
                da.SelectCommand = myComm;
                myDataSet = new DataSet(sTableName);
                da.Fill(myDataSet);
                CloseConnection();
                Errm = "OK";
                return myDataSet;
            }
            catch (Exception ex)
            {
                Errm = ex.Message;
                return null;
            }
        }

		internal void CloseConnection()
		{
			// Close the Connection when the connection is opened.
			if (kapcsolodva == true)
			{
				mySQLServerConn.Close();
				kapcsolodva = false;
			}
		}

		protected int myExecuteScalar(string myQuery,ref string ErrMess) 
		{
			int iScalar = 0; 
			this.OpenConnection();
			myComm = new SqlCommand(myQuery, mySQLServerConn);
			try 
			{
				iScalar = Convert.ToInt32(myComm.ExecuteScalar());
			}
			catch (Exception e)
			{
				ErrMess = e.Message;
								
			}
			this.CloseConnection();
			return iScalar;	
			
		}
		protected DataSet myFillDataSetFromString(String sSQL, String sTableName, ref SqlDataAdapter da, ref string Errm) 
		{
			//da = new SqlDataAdapter();
			try
			{
		//		da.TableMappings.Add("Table", sTableName);
				OpenConnection();
				myComm = new SqlCommand(sSQL,mySQLServerConn);
				myComm.CommandType = CommandType.Text;
				da.SelectCommand= myComm;
				DataSet ds= new DataSet(sTableName);
				da.Fill(ds);
				CloseConnection();
				Errm = "OK";
				return ds;
			}
			catch(Exception ex)
			{
				Errm = ex.Message;
				return null;
			}
		}
		protected string myFillExistingDataSet(String sSQL, String sTableName, ref SqlDataAdapter da, ref DataSet ds) 
		{
			//da = new SqlDataAdapter();
			String Errm;
			try
			{
				da.TableMappings.Add("Table", sTableName);
				OpenConnection();
				myComm = new SqlCommand(sSQL,mySQLServerConn);
				myComm.CommandType = CommandType.Text;
				da.SelectCommand= myComm;
				//DataSet ds= new DataSet(sTableName);
				da.Fill(ds);
				CloseConnection();
				Errm = "OK";
				return Errm;
			}
			catch(Exception ex)
			{
				Errm = ex.Message;
				return Errm;
			}
		}

		protected void myInsertCommand(string sInsert, SqlDataAdapter da, ref string Errm)
		{
			try
			{
				OpenConnection();
				da.InsertCommand = new SqlCommand(sInsert,mySQLServerConn);
				da.InsertCommand.ExecuteNonQuery();
				CloseConnection();
				Errm = "OK";
			}
			catch( Exception ex )
			{
				Errm = ex.ToString();
			}
			CloseConnection();
		}
		protected void MyDeleteComm(string sDelete, SqlDataAdapter da, ref string Err)
		{
			try
			{
				OpenConnection();
				da.DeleteCommand = new SqlCommand(sDelete,mySQLServerConn);
				da.DeleteCommand.ExecuteNonQuery();
				Err = "OK";
			}
			catch( Exception ex )
			{
				Err = ex.ToString();
			}
			CloseConnection();
		}
	}

	}




